package Communication;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;





import View.CommentReportResultView;
import View.CustomerReportView;
import Controller.SystemController;
import DB.CustomerQuery;
import Entity.Category;
import DB.CategoryQuery;
import DB.CitiesQuery;
import DB.CustomerHistoryQuery;
import DB.ProductQuery;
import DB.ReportQuery;
import DB.SaleQuery;
import Entity.CommentReport;
import Entity.Cities;
import Entity.Customer;
import Entity.CustomerCard;
import Entity.CustomerReport;
import Entity.Sale;
import Entity.Worker;
import Entity.Product;
//adi
import Entity.Pattern;
import DB.PatternQuery;

/**
 * 
 * this class open connection and listen to requests and also  handle all the massages from client 
 *
 */
public class Server extends AbstractServer {
	/**
	 * Required instances and attributes
	 */
	private String userName = null;
	private String password = null;
	private String host=null;

	ArrayList<Object> backMsg = new ArrayList<Object>();
	final public static int DEFAULT_PORT = 5555;
	private MySqlConnection mySQL = null;
	static CommentReport CommentRpt;
	static CustomerReport CustomerRpt;

	/**
	 * Default Constructor
	 * @param port
	 */
	public Server(int port) {
		super(port);
		mySQL = new MySqlConnection();

	}

	/**
	 * This method handle all the requests from client according to the type of request
	 * this method also write and read from db
	 * sending results to client
	 */
	@SuppressWarnings("unchecked")
	protected void handleMessageFromClient(Object msg, ConnectionToClient client) throws IOException {

		MessageHandle message = (MessageHandle) msg;
		String query = null;
		MessageHandle back = null;

		try {

			if (message.getType() != MessageHandle.CONFIG) {
				ResultSet rs = null;
				ResultSet rsTemp = null;
				// If the query is to update the DB
				if (message.isUpdateDB()) {
					query = (String) (message.getData());
					mySQL.updateDB(query);
				} else {
					// The query is to get data from DB
					/**
					 * switch the type of the massage and handle according to it
					 */
					switch (message.getType()) {

					/**
					 * get worker details from db and also checks if worker exist
					 */
					case MessageHandle.WORKER:
						query = (String) (message.getData());
						rs = mySQL.getFromDB(rs, query);
						while (rs.next()) {
							Worker worker = new Worker(rs.getString(1), rs.getString(2),
									rs.getString(3), rs.getString(4),rs.getString(5),
									rs.getString(6),rs.getInt(7),rs.getInt(8));
							back = new MessageHandle(worker,
									MessageHandle.WORKER, false);
							client.sendToClient(back);
						}
						break;
						/**
						 *  run new sale with existing pattern
						 */
					case MessageHandle.SALE:

						Sale sale = (Sale) (message.getData());
						query=SaleQuery.getExistSaleQuery(sale .getSaleID());
						rs = mySQL.getFromDB(rs, query);

						if(rs.next()==false) {//sale not exist
							query=SaleQuery.getPatternPerSale(sale.getSalePatternID());
							rs = mySQL.getFromDB(rs, query);

							if(rs.next()!=false){//pattern exist in db
								query=SaleQuery.getPatternFromSaleList(sale.getSalePatternID());
								rs = mySQL.getFromDB(rs, query);

								if(rs.next()==false){//pattern not exist in list
									query=SaleQuery.InsertNewSale(sale.getSaleID(), sale.getSalePatternID())	;
									mySQL.updateDB(query);
									//ADD TO LIST
									String str = "new";
									back = new MessageHandle(str,
											MessageHandle.SALE, false);
								}
								else{
									String str = "pattern already exist";
									back = new MessageHandle(str,
											MessageHandle.SALE, false);
								}
							}
							else{
								String str = "pattern not exist";
								back = new MessageHandle(str,
										MessageHandle.SALE, false);
							}
						}
						else {

							String str = "sale already exist";
							back = new MessageHandle(str,
									MessageHandle.SALE, false);
						}

						client.sendToClient(back);

						break;

						/**
						 * add new customer to db
						 */
					case MessageHandle.ADD_CUSTOMER:			
						Customer cust = (Customer) message.getData();

						query =CustomerQuery.getCustomerQuery(cust.getCustomerID());


						rs = mySQL.getFromDB(rs, query);

						if(rs.next() == false){			//customer not exist
							query = CustomerQuery.InsertCustomerQuery(cust);
							mySQL.updateDB(query);						
							String str = "new";
							back = new MessageHandle(str,
									MessageHandle.ADD_CUSTOMER, false);
						}	
						else {							//customer exist
							String str = "Add Customer Failed: Customer Already exist";
							back = new MessageHandle(str,
									MessageHandle.ADD_CUSTOMER, false);
						}						

						client.sendToClient(back);
						break;

						/**
						 * get all exist cities from db
						 */
					case MessageHandle.GET_CITIES: 
						rs = mySQL.getFromDB(rs, CitiesQuery.getAllCities());
						rs.last();						//go to the end of the table
						int totalRows = rs.getRow();				//rreturn count of rows
						rs.beforeFirst();							//go back to first row
						String  [] cities = new String[totalRows];
						for (int i = 0; i < totalRows; i++)
							cities[i] = new String();

						int rowCount = 0;
						String strCities;
						while (rs.next()) {
							strCities= rs.getString(2);
							cities[rowCount] = strCities;
							rowCount++;
						}
						back= new MessageHandle(cities, MessageHandle.GET_CITIES, false);
						back.setMsg("GetCities");
						client.sendToClient(back);
						break;


						/**
						 * get all customers history information
						 */
					case MessageHandle.GET_CUSTOMERS: 
						String msgCustomers =  (String) message.getMsg();
						String[] parts = msgCustomers.split("-");
						String workerid = parts[0]; 
						String saleid = parts[1];
						rs = mySQL.getFromDB(rs, CustomerHistoryQuery.getAllCustomers(workerid,saleid));
						rs.last();						//go to the end of the table
						int TotalRows = rs.getRow();				//return count of rows
						rs.beforeFirst();							//go back to first row
						String  [] customers = new String[TotalRows];
						for (int i = 0; i < TotalRows; i++)
							customers[i] = new String();

						int RowCount = 0;
						String strCustomers;
						while (rs.next()) {
							strCustomers= rs.getString(1);
							customers[RowCount] = strCustomers;
							RowCount++;
						}
						back= new MessageHandle(customers, MessageHandle.GET_CUSTOMERS, false);
						back.setMsg("GetCustomers");
						client.sendToClient(back);
						break;

						/**
						 * get all existing products from db
						 */
					case MessageHandle.GET_PRODUCTS: 
						rs = mySQL.getFromDB(rs, ProductQuery.getAllProducts());
						rs.last();						//go to the end of the table
						int Total_Rows = rs.getRow();		//return count of rows
						rs.beforeFirst();							//go back to first row
						String  [] products = new String[Total_Rows];
						for (int i = 0; i < Total_Rows; i++)
							products[i] = new String();
						rs.beforeFirst();
						int Row_Count = 0;
						String strProducts;
						while (rs.next()) {
							strProducts= rs.getString(1);
							products[Row_Count] = strProducts;
							Row_Count++;
						}
						back= new MessageHandle(products, MessageHandle.GET_PRODUCTS, false);
						back.setMsg("GetProducts");
						client.sendToClient(back);
						break;

						/**
						 * update customer details
						 */
					case MessageHandle.UPDATE_GET_CUSTOMER:

						switch (message.getMsg()) {
						case "FindCustomer":
							String id = ((Customer) message.getData()).getCustomerID();
							query =CustomerQuery.getCustomerQuery(id);

							rs = mySQL.getFromDB(rs, query);

							if(rs.next() == false){			//customer not exist
								back = new MessageHandle(message.getData(),
										MessageHandle.UPDATE_GET_CUSTOMER, false);
								back.setMsg("NotExist");
							}	
							else {							//customer exist
								Customer customer =  new Customer();
								customer.setCustomerID(Integer.toString(rs.getInt(1)));
								customer.setCfirstName(rs.getString(2));
								customer.setClastName(rs.getString(3));
								customer.setCity(rs.getString(5));
								customer.setPhoneNumber(rs.getString(6));
								back = new MessageHandle(customer,
										MessageHandle.UPDATE_GET_CUSTOMER, false);
								back.setMsg("Exist");
							}					 
							client.sendToClient(back);
							break;
						case "UpdateCustomer":
							query =CustomerQuery.updateCustomer((Customer)message.getData());
							mySQL.updateDB(query);
							back = new MessageHandle((Customer)message.getData(),
									MessageHandle.UPDATE_GET_CUSTOMER, false);
							back.setMsg("UpdateSuccess");
							client.sendToClient(back);
							break;
						default:
							break;
						}
						break;

						/**
						 * handle all catalog settings according to the categories
						 */

					case MessageHandle.CATEGORY:
						Category ctg = (Category) message.getData();

						query = CategoryQuery.getCategoryDB(ctg.getTypeId());
						rs = mySQL.getFromDB(rs, query);
						if(rs.next() == false) 
						{//category id not exist in db

							query = CategoryQuery.getCategoryNameDB(ctg.getTypeName(), ctg.getCategoryName());
							rs = mySQL.getFromDB(rs, query);
							if(rs.next() == false)
							{//category name and type not exist in db

								query= CategoryQuery.getProductDB(ctg.getProductID());
								rs = mySQL.getFromDB(rs, query);
								if(rs.next() != false) 
								{//product exist in db
									query = CategoryQuery.setCategoryDB(ctg);
									mySQL.updateDB(query);						//add new category to category table
									query = CategoryQuery.setCategory_productDB(ctg);
									mySQL.updateDB(query);       //add new category to category_product table
									String str = "new";
									back = new MessageHandle(str,
											MessageHandle.CATEGORY, false);
								}
								else
								{
									String str = "product not exists";
									back = new MessageHandle(str,
											MessageHandle.CATEGORY, false);
								}


							

						}
						else
						{							//category name  exist
							String str = "category name and type  exist";
							back = new MessageHandle(str,
									MessageHandle.CATEGORY, false);
						}
					}
					else
					{							//category name  exist
						String str = "category id exist";
						back = new MessageHandle(str,
								MessageHandle.CATEGORY, false);
					}


					client.sendToClient(back);
					break;	

					/**
					 * insert new product to db
					 */


				case MessageHandle.PRODUCT:
					Product prd = (Product) message.getData();

					query =ProductQuery.checkExistProduct(prd.getProductID());


					rs = mySQL.getFromDB(rs, query);

					if(rs.next() == false){			
						query = ProductQuery.InsertProduct(prd);
						mySQL.updateDB(query);						
						String str = "new";
						back = new MessageHandle(str,
								MessageHandle.PRODUCT, false);
					}	
					else {							
						String str = "Add Product Failed: Product Already exist";
						back = new MessageHandle(str,
								MessageHandle.PRODUCT, false);
					}						

					client.sendToClient(back);
					break;	

					/**
					 * handle new running sale and contact to customers
					 */
				case MessageHandle.HANDLE_SALE:

					String SaleID = (String) message.getMsg();
					query=SaleQuery.getExistSaleQuery(SaleID);

					rs = mySQL.getFromDB(rs, query);
					if(rs.next() != false) {

						String str = "exist";
						back = new MessageHandle(str,
								MessageHandle.HANDLE_SALE, false);
					}						

					else
					{
						String str = "sale not exists";
						back = new MessageHandle(str,
								MessageHandle.HANDLE_SALE, false);
					}
					client.sendToClient(back);
					break;
					/**
					 * entering new pattern for existing product
					 */
				case MessageHandle.PATTERN:
					Pattern Ptr = (Pattern) message.getData();
					query= PatternQuery.getPatternQuery(Ptr.getPatternID());
					rs = mySQL.getFromDB(rs, query);
					if(rs.next() == false) 
					{//pattern not exist
						query= PatternQuery.getProductDB(Ptr.getProductID());
						rs = mySQL.getFromDB(rs, query);
						if(rs.next() != false) 
						{//product exist
							query = PatternQuery.InsertPatternQuery(Ptr);
							mySQL.updateDB(query);						//add new pattern
							String str = "new";
							back = new MessageHandle(str,
									MessageHandle.PATTERN, false);
						}
						else
						{
							String str = "product not exists";
							back = new MessageHandle(str,
									MessageHandle.PATTERN, false);
						}


					}	
					else 
					{							//pattern exist
						String str = "exist";
						back = new MessageHandle(str,
								MessageHandle.PATTERN, false);
					}


					client.sendToClient(back);
					break;



					/**
					 * distribute customers to sales man according to sale operation
					 */
				case MessageHandle.PATTERN_FOR_SALE:
					int MinAge=0;
					int MaxAge=0;
					String City=" ";
					String CustomerID=" ";
					String  [] salsemans={""};
					int Total_rows_salesman=0;
					int RowCountSalesman = 0;
					Pattern Ptr2 = (Pattern) message.getData();
					query= SaleQuery.getPatternPerSale(Ptr2.getPatternID()); //find pattern
					rs = mySQL.getFromDB(rs, query);
					if (rs.next() == false) {
						String str = "not working";
						back = new MessageHandle(str,
								MessageHandle.PATTERN_FOR_SALE, false);
					}
					else{
						MinAge = rs.getInt("MinAge");//GET MIN AGE FROM DB
						MaxAge = rs.getInt("MaxAge");
						City = rs.getString("City");
						if ((MinAge==0) ||(MaxAge==0)||(City==null) )
						{
							String str = "fields missing";
							back = new MessageHandle(str,
									MessageHandle.PATTERN_FOR_SALE, false);
						}
						else{
							query= SaleQuery.getPatternFromSaleList(Ptr2.getPatternID()); //find saleid for pattern
							rs = mySQL.getFromDB(rs, query);

							if (rs.next() == false) //patter for sale id is not exist
							{
								System.out.println("pattern for sale id is not ok");
							}


							else{
								SaleID = rs.getString("SaleID");	
								query= SaleQuery.findCustomersInDB(MinAge,MaxAge,City); //find customers for pattern
								rsTemp = mySQL.getFromDB(rsTemp, query);
								rs= rsTemp;
								if (rs.next()==false){//customer dont exist
									String str = "customers not exists";
									back = new MessageHandle(str,
											MessageHandle.PATTERN_FOR_SALE, false);
								}
								else{
									rs.last();						//go to the end of the table
									int Total_rows_customers = rs.getRow();	//return count of rows
									query= SaleQuery.getSalesWorkers(); //find how much salsworkes
									rs= mySQL.getFromDB(rs, query);
									if(rs.next() == false) {//salseman not exist
										String str = "salesman not exists";
										back = new MessageHandle(str,
												MessageHandle.PATTERN_FOR_SALE, false);
									}
									else{
										rs.last();						//go to the end of the table
										Total_rows_salesman = rs.getRow();				//return count of rows
										rs.beforeFirst();
										salsemans = new String[Total_rows_salesman];
										for (int i = 0; i < Total_rows_salesman; i++)
											salsemans[i] = new String();


										String strsalesman;
										while (rs.next()) {
											strsalesman= rs.getString(1);//gets workerid
											salsemans[RowCountSalesman] = strsalesman;
											RowCountSalesman++;
										}

										rsTemp.first();
										CustomerID = rsTemp.getString(1);
										int i=0;
										query= SaleQuery.InsertCustomersHistoryQuery(CustomerID,SaleID,salsemans[i]);
										if (i+1==Total_rows_salesman||Total_rows_salesman==1) i=0;
										else i++;
										mySQL.updateDB(query);						//add new Customer
										while (rsTemp.next()){
											CustomerID = rsTemp.getString(1);
											query= SaleQuery.InsertCustomersHistoryQuery(CustomerID,SaleID,salsemans[i]);
											mySQL.updateDB(query);						//add new Customer

											if (i+1==Total_rows_salesman||Total_rows_salesman==1) i=0;
											else i++;
										}
										String str = "new_customer";
										back = new MessageHandle(str,
												MessageHandle.PATTERN_FOR_SALE, false);

									}
								}
							}

						}
					}
					client.sendToClient(back);
					break;

					/**
					 * produce comment report
					 */
				case MessageHandle.COMMENT_REPORT:
					String SaleId=(String) message.getData();
					query=SaleQuery.getExistSaleQuery(SaleId) ;
					rs = mySQL.getFromDB(rs, query);
					if(rs.next() == false) 
					{back = new MessageHandle("SaleID not Exist",
							MessageHandle.COMMENT_REPORT, false);
					}
					else{

						CommentRpt=new CommentReport();
						query= ReportQuery.getTotalCustomersCommentReport(SaleId);
						rs = mySQL.getFromDB(rs, query);
						if(rs.next() != false) 
						{
							CommentRpt.setTotalCustomers(rs.getInt(1));
						}
						query= ReportQuery.getTotalSalesCommentReport(SaleId);

						rs = mySQL.getFromDB(rs, query);
						if(rs.next() != false) 
						{
							CommentRpt.setTotalSales(rs.getInt(1));

						}
						query= ReportQuery.getTotalTimeCommentReport(SaleId);
						rs = mySQL.getFromDB(rs, query);
						while(rs.next() != false) 
						{
							CommentRpt.setTotalTime(rs.getInt(1));


						}
						query= ReportQuery.getCommentToCommentReport(SaleId);
						rs = mySQL.getFromDB(rs, query);
						while(rs.next() != false) 
						{
							CommentRpt.AddCommentToList(rs.getString(1));

						}

						back = new MessageHandle("Success",
								MessageHandle.COMMENT_REPORT, false);
						new CommentReportResultView(CommentRpt,SaleId);
					}

					client.sendToClient(back);
					break;

					/**
					 * produce customer report
					 */
				case MessageHandle.CUSTOMER_REPORT:

					query=ReportQuery.getCustomersList() ; //checks if there are customers
					rs = mySQL.getFromDB(rs, query);
					int totalbuy=0;
					if(rs.next() == false) 
					{back = new MessageHandle("no cutomers",
							MessageHandle.CUSTOMER_REPORT, false);
					}
					else{
						rs.last();
						CustomerRpt=new CustomerReport();
						CustomerRpt.setTotalCustomers(rs.getRow()); //total customers = total rows in query
						query= ReportQuery.getCustomersListTotalBought();
						rs = mySQL.getFromDB(rs, query);
						if(rs.next() != false)  // more then 1 bogut
						{
							rs.last();
							CustomerRpt.setTotalBought(rs.getRow());
							totalbuy=rs.getRow(); //totalbuy = how many customers bought

							rs.beforeFirst(); // goes to the beginning

							rs.next();
							do
							{
								int totalbuout = rs.getInt(2);
								CustomerRpt.setTotalBought(totalbuout);
								int totalsum= rs.getInt(4);
								float bogutpercent=0;
								if (totalbuout>0) bogutpercent = (float)totalbuout/totalsum;
								CustomerRpt.setTotalPercent(bogutpercent);
								CustomerRpt.AddCustomerstToList(rs.getString(1),totalbuout,bogutpercent);
							}while(rs.next());
							back = new MessageHandle("Success",
									MessageHandle.CUSTOMER_REPORT, false);
							new CustomerReportView(CustomerRpt);
						}
					}
					client.sendToClient(back);
					break;



					/**
					 * add new city to db
					 */
				case MessageHandle.ADD_CITY:
					Cities Cty = (Cities) message.getData();
					query=CitiesQuery.checkExistCity(Cty.getCityID());
					rs = mySQL.getFromDB(rs, query);
					if(rs.next() == false){			
						query = CitiesQuery.insertCity(Cty.getCityID(),Cty.getCityName());
						mySQL.updateDB(query);						
						String str1 = "new city"; 
						back = new MessageHandle(str1,
								MessageHandle.ADD_CITY, false);
					}
					else{
						String str1 = "exist city";
						back = new MessageHandle(str1,
								MessageHandle.ADD_CITY, false);
					}
					client.sendToClient(back);
					break;

				case MessageHandle.DELETE_CITY:
					Cities Cty1 = (Cities) message.getData();
					query=CitiesQuery.checkExistCity(Cty1.getCityID());
					rs = mySQL.getFromDB(rs, query);
					if(rs.next() == false){			

						String str1 = "not exist city";
						back = new MessageHandle(str1,
								MessageHandle.DELETE_CITY, false);
					}

					else{
						query=CitiesQuery.deleteCity(Cty1.getCityID(), Cty1.getCityName());
						mySQL.updateDB(query);	
						String str1 = "delete";
						back = new MessageHandle(str1,
								MessageHandle.DELETE_CITY, false);

					}
					client.sendToClient(back);
					break;

					/**
					 * update customer history details
					 */


				case MessageHandle.UPDATE_GET_CUSTOMER_HISTORY:
					String CstSaleID = ((CustomerCard) message.getData()).getCSaleID();
					switch (message.getMsg()) {
					case "FindCustomer":
						String CstID = ((CustomerCard) message.getData()).getCCustomerID();
						query =CustomerHistoryQuery.getCustomerCardQuery(CstID,CstSaleID);
						rs = mySQL.getFromDB(rs, query);

						if(rs.next() == false){			//customerCard not exist
							back = new MessageHandle(message.getData(),
									MessageHandle.UPDATE_GET_CUSTOMER_HISTORY, false);
							back.setMsg("NotExist");
						}	
						else {							//customer exist
							CustomerCard customerCard =  new CustomerCard();
							customerCard.setCContactCust(rs.getInt(4));
							customerCard.setCProductName(rs.getString(5));
							customerCard.setCustResponse(rs.getString(6));
							customerCard.setCustApproval(rs.getInt(7));
							customerCard.setCallTime(rs.getInt(8));

							back = new MessageHandle(customerCard,
									MessageHandle.UPDATE_GET_CUSTOMER_HISTORY, false);
							back.setMsg("Exist");
						}					 
						client.sendToClient(back);
						break;



					case "UpdateCustomerCard":
						query =CustomerHistoryQuery.updateCustomerCard((CustomerCard) message.getData(),CstSaleID);
						mySQL.updateDB(query);
						query =CustomerHistoryQuery.findCustomerbought((CustomerCard) message.getData(),CstSaleID);
						rs = mySQL.getFromDB(rs, query);
						if (rs.next()!=false){          /**customer  exist**/
							int cstBought = rs.getInt(1);
							query =CustomerHistoryQuery.updateCustomerInfo((CustomerCard) message.getData(),CstSaleID,cstBought);
							mySQL.updateDB(query);
						}
						back = new MessageHandle((CustomerCard)message.getData(),
								MessageHandle.UPDATE_GET_CUSTOMER_HISTORY, false);
						back.setMsg("UpdateSuccess");
						client.sendToClient(back);
						break;
					default:
						break;
					}
					break;	





				}
			}

		} else {

			/**
			 *  handle configuration and connection 
			 */

			ArrayList<String> data = (ArrayList<String>) message.getData();

			userName = new String(data.get(0));
			password = new String(data.get(1));
			host=new String(data.get(2));
			mySQL.setPassword(password);
			mySQL.setUserName(userName);
			mySQL.setHost(host);
			mySQL.connect();

		}


	} catch (SQLException e) {
		System.out.print(""+e.getMessage());
		e.printStackTrace();
	}
}

protected void serverStarted() {
	System.out.println("Server listening for connections on port "
			+ getPort());
	SystemController.displayMessage("Server listening for connections on port "
			+ getPort());
}

protected void serverStopped() {
	System.out.println("Server has stopped listening for connections.");
}

/**
 * start server
 * @param args
 */
public static void main(String[] args) {

	int port = DEFAULT_PORT; // Port to listen on

	try {
		port =  Integer.parseInt(args[0]);

	} catch (Throwable t) {

		port =DEFAULT_PORT;
	}

	Server server = new Server(port);

	try {
		server.listen(); /** Start listening for connections **/
	} catch (Exception ex) {
		System.out.println("ERROR - Could not listen for clients!");
	}
}

}
